Wrangling data

What do we mean by data wrangling?

Lots of possible definitions, but generally refers to all the transformations you might need to do to get data into a form that is ready for further analysis.

Common tasks

• Create a new column based on calculations in your data

• Select only certain parts of your data

• Do calculations on subsets of your data

• Modify text data

• Iterate over your data

• Merge data from multiple sources

Common tasks

• Create a new column based on calculations in your data: mutate()

• Select only certain columns or rows of your data: filter(), select()

• Do calculations on subsets of your data: group_by(), summarise()

• Modify text data: stringr functions

• Iterate over your data: for loops, apply(), purrr::map()

• Merge data from multiple sources: join()

Before we start…

How would you describe your prior experience with R?

1. I just downloaded it for the first time.

2. I use it, but it makes me uncomfortable.

3. I’m comfortable with it, here to learn about the tidyverse.

How comfortable are you with programming concepts like for loops and writing functions?

1. No idea what you’re talking about.

2. I’ve heard of and used these things, but they’re a stretch.

3. Easy peasy.

Let’s jump in!

• Disclaimer: I don’t know everything, and there’s always more to learn!

The tidyverse

A collection of R packages designed for data wrangling and visualization, built for tidying your data and working with tidy data.

library(tidyverse)

Why the tidyverse?

• Elegant, consistent way of organizing data

• First argument to each function is typically a data frame (consistency)

• More like reading English than base R code

• Lots of packages that work well with tidy principles
  • Network graphs, animations, more
  • http://www.ggplot2-exts.org/gallery/
  • https://www.tidytextmining.com/index.html
• Generally a friendly, enthusiastic community
  • https://community.rstudio.com/t/welcome-to-the-rstudio-community/8

Why not the tidyverse?

• More likely to change than base R

Tidy data

1. Each variable forms a column.

2. Each observation forms a row.

3. Each type of observational unit forms a table. (https://cran.r-project.org/web/packages/tidyr/vignettes/tidy-data.html)

Tidy data example

An untidy example:

Tidy data example

Tidied:

Is tidy always better?

Sometimes messy can be good!

When not to tidy…

• Data entry

• Data with matrix operations (e.g., statistical modeling)

• Nice to know your options

Example data

Read data using read_csv().

ufo <- read_csv("../data/ufo_dat.csv")

## Parsed with column specification:
## cols(
##   datetime = col_character(),
##   city = col_character(),
##   state = col_character(),
##   country = col_character(),
##   shape = col_character(),
##   `duration (seconds)` = col_integer(),
##   `duration (hours/min)` = col_character(),
##   comments = col_character(),
##   `date posted` = col_character(),
##   latitude = col_double(),
##   longitude = col_double()
## )

## Warning in rbind(names(probs), probs_f): number of columns of result is not
## a multiple of vector length (arg 1)

## Warning: 266 parsing failures.
## row # A tibble: 5 x 5 col     row col                expected               actual file              expected   <int> <chr>              <chr>                  <chr>  <chr>             actual 1  1606 duration (seconds) no trailing characters .5     '../data/ufo_dat… file 2  1653 duration (seconds) no trailing characters .5     '../data/ufo_dat… row 3  1660 duration (seconds) no trailing characters .1     '../data/ufo_dat… col 4  1683 duration (seconds) no trailing characters .5     '../data/ufo_dat… expected 5  2039 duration (seconds) no trailing characters .05    '../data/ufo_dat…
## ... ................. ... .......................................................................... ........ .......................................................................... ...... .......................................................................... .... .......................................................................... ... .......................................................................... ... .......................................................................... ........ ..........................................................................
## See problems(...) for more details.

ufo1 <- read.csv("../data/ufo_dat.csv")

A word on file structures

• Many structures work, but you should definitely have one.

• Consider a “recipe” script.

• Try to avoid:
  • setwd()
  • read.csv("adrienne/cool_project1/version6/sdfljk.csv")

A word on file structures

Let’s load some data…

read_csv() / read.csv()

• speed difference:

system.time(ufo <- read_csv("../data/ufo_dat.csv"))

##    user  system elapsed 
##   0.525   0.023   0.553

system.time(ufo1 <- read.csv("../data/ufo_dat.csv"))

##    user  system elapsed 
##   2.722   0.038   2.768

• read_csv() is “lazy and surly” (Wickham).

• read.csv(): stringsAsFactors = TRUE, uses rownames

Take a look

View(ufo)

The pipe: %>%

• Read it in your head as “then”
  • as in, select part of your data, then arrange it in order.
• Use with functions that take a data frame as their first argument.
  • Works with: head()
  • Not with: mean()

Common data wrangling tasks

Rename columns

ufo <- ufo %>% 
  rename("duration_sec" = `duration (seconds)`)

This is the same as:

ufo <- rename(ufo, "duration_sec" = `duration (seconds)`)

Create a new column:

mutate()

ufo <- ufo %>%
  mutate(duration_min = duration_sec/60) %>%
  mutate(duration_hr = duration_min/60)

kable(head(ufo[, c("duration_sec", "duration_min", "duration_hr")]))

Where are the quotes?!

ufo <- ufo %>%
  mutate(duration_min = duration_sec/60) %>%
  mutate(duration_hr = duration_min/60)

• In tidyverse functions, column names typically don’t get quotes.

• This can be a little inconsistent:
  • Check the examples in documentation if unsure: ?mutate
  • If it becomes a problem, check out tidyeval: https://dplyr.tidyverse.org/articles/programming.html

mutate()

Challenge: Add a column that gives the duration in years. (Hint: there are 8760 hours in a year.)

Challenge answer

ufo <- ufo %>% 
  mutate(duration_years = duration_hr/8760)

Subset your data:

filter(), select()

ufo %>% 
  filter(duration_hr > 10) %>%
  select(datetime, duration_sec, duration_hr, `duration (hours/min)`) %>%
  arrange(desc(duration_hr)) %>%
  head()

## # A tibble: 6 x 4
##   datetime        duration_sec duration_hr `duration (hours/min)`
##   <chr>                  <int>       <dbl> <chr>                 
## 1 10/1/1983 17:00     97836000      27177. 31 years              
## 2 6/3/2010 23:30      82800000      23000. 23000hrs              
## 3 9/15/1991 18:00     66276000      18410. 21 years              
## 4 4/2/1983 24:00      52623200      14618. 2 months              
## 5 8/10/2012 21:00     52623200      14618. 2 months              
## 6 8/24/2002 01:00     52623200      14618. 2 months

31 years?!

comment <- ufo %>% 
  filter(duration_sec > 90000000) %>% 
  select(comments)
print(comment$comments)

## [1] "Firstly&#44 I was stunned and stared at the object for what seemed minutes&#44 but probably was only seconds. My first inclination was to bec"

Challenge: Get only the observations shorter than a duration of your choice. Save the results to a new data frame called short_sight.

Challenge answer

short_sight <- ufo %>% filter(duration_hr < 1)

Do calculations on subsets:

group_by() %>% summarise()

For example, how many sightings were there per state?

n_per_state <- ufo %>%
  group_by(state) %>% 
  count(sort = T)

Calculate on subsets

Another example

mean_lon <- ufo %>% 
  group_by(state) %>%
  summarise(lon = mean(longitude))

Iterate over your data:

for loops, apply(), purrr::map()

When to iterate:

• Anytime you need to do something more than twice (ish)

• Examples:
  • downloading many files from the internet
  • analyzing several scenarios
  • calculations for each year in a time series

for loops

• Advantages: many other programming languages also have for loops.

• Disadvantages: can be bulky and/or slow compared to other options.

Example for loops

How many UFOs were observed in each state?

states <- unique(ufo$state)
answer_df <- data.frame(state = states, n = NA)

for(i in 1:length(states)){
  temporary_data <- ufo %>% filter(state == states[i])
  answer_df$n[i] <- nrow(temporary_data)
}

kable(head(answer_df))

Any differences from our group_by() %>% summarise() approach?

Example apply function

How many UFOs were observed in each state?

states <- unique(ufo$state)

count_obs <- function(state_id){
  nrow(ufo %>% filter(state == state_id))
}

counts <- lapply(states, count_obs)

counts[1:5]

## [[1]]
## [1] 3677
## 
## [[2]]
## [1] 0
## 
## [[3]]
## [1] 353
## 
## [[4]]
## [1] 1193
## 
## [[5]]
## [1] 968

Example map function

This is the tidyverse way…

How many UFOs were observed in each state?

states <- unique(ufo$state)

count_obs <- function(state_id){
  nrow(ufo %>% filter(state == state_id))
}

ans_list <- map(states, count_obs)
ans_vect <- map_int(states, count_obs)

More with map

head(mtcars)

##                    mpg cyl disp  hp drat    wt  qsec vs am gear carb
## Mazda RX4         21.0   6  160 110 3.90 2.620 16.46  0  1    4    4
## Mazda RX4 Wag     21.0   6  160 110 3.90 2.875 17.02  0  1    4    4
## Datsun 710        22.8   4  108  93 3.85 2.320 18.61  1  1    4    1
## Hornet 4 Drive    21.4   6  258 110 3.08 3.215 19.44  1  0    3    1
## Hornet Sportabout 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2
## Valiant           18.1   6  225 105 2.76 3.460 20.22  1  0    3    1

models <- mtcars %>% 
  split(.$cyl) %>% 
  map(function(df) lm(mpg ~ wt, data = df))

** Example stolen directly from R for Data Science: http://r4ds.had.co.nz/iteration.html#the-map-functions

What did we get?

A list of three linear models…

str(models, max.level = 1)

## List of 3
##  $ 4:List of 12
##   ..- attr(*, "class")= chr "lm"
##  $ 6:List of 12
##   ..- attr(*, "class")= chr "lm"
##  $ 8:List of 12
##   ..- attr(*, "class")= chr "lm"

What did we get?

summary(models[[1]])

## 
## Call:
## lm(formula = mpg ~ wt, data = df)
## 
## Residuals:
##     Min      1Q  Median      3Q     Max 
## -4.1513 -1.9795 -0.6272  1.9299  5.2523 
## 
## Coefficients:
##             Estimate Std. Error t value Pr(>|t|)    
## (Intercept)   39.571      4.347   9.104 7.77e-06 ***
## wt            -5.647      1.850  -3.052   0.0137 *  
## ---
## Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
## 
## Residual standard error: 3.332 on 9 degrees of freedom
## Multiple R-squared:  0.5086, Adjusted R-squared:  0.454 
## F-statistic: 9.316 on 1 and 9 DF,  p-value: 0.01374

Extract coefficients

names(models[[1]])

##  [1] "coefficients"  "residuals"     "effects"       "rank"         
##  [5] "fitted.values" "assign"        "qr"            "df.residual"  
##  [9] "xlevels"       "call"          "terms"         "model"

Extract coeffients

map(models, "coefficients")

## $`4`
## (Intercept)          wt 
##   39.571196   -5.647025 
## 
## $`6`
## (Intercept)          wt 
##   28.408845   -2.780106 
## 
## $`8`
## (Intercept)          wt 
##   23.868029   -2.192438

** Caveat: May not be a good idea statistically…

Merge data from multiple sources: join()

Are number of UFO sightings per year associated with economic conditions?

join()

First, get economic data: ?economics

kable(head(economics))

join()

Make a year column in the UFO dataset. This requires a brief detour into lubridate

library(lubridate)

ufo <- ufo %>% 
  separate(datetime, into = c("date", "time"), sep = " ") %>%
  mutate(date = mdy(date)) %>% 
  mutate(year = year(date))

join()

Let’s see what we got out of that:

ufo %>% 
  select(date, time, year) %>% 
  head() %>% 
  kable()

join()

We’ll need to know how many UFO sightings there were each year in our dataset.

Challenge: how would you do this?

• Consider:
  • for loops
  • apply functions
  • map functions
  • group_by() %>% summarise()

join()

ufo_counts <- ufo %>% 
  group_by(year) %>%
  count()

kable(head(ufo_counts))

join()

We’ll also want economic summaries by year.

econ_summary <- economics %>% 
  mutate(year = year(date)) %>% 
  group_by(year) %>% 
  summarise_all(mean) %>%
  ungroup() %>%
  select(-date)

At last: join()

df_new <- left_join(ufo_counts, econ_summary, by = "year") %>% 
  filter(year >= min(econ_summary$year)) 
kable(head(df_new))

Why join?

How do joins work?

Wide to long: melt

What if we wanted to plot all these economic variables at the same time?

Wide to long: melt

library(reshape2)
df_long <- df_new %>% 
  melt(id.vars = c("year", "n"))

kable(head(df_long))

Wide to long: melt

ggplot(df_long, aes(x = value, y = n)) + 
  geom_point() +
  geom_smooth() + 
  facet_wrap(~variable, scales = "free_x")

Text data: stringr functions

Which UFO observations refer to a color?

ufo <- ufo %>% mutate(comments = tolower(comments))

colors <- c(" red ", " orange ", " yellow ", " green ", " blue ", " purple ", " pink ", " white ", " black ")

ufo_colors <- ufo %>% 
  mutate(color_ans = str_detect(comments, paste(colors, collapse = "|"))) %>% 
  filter(color_ans == T)

Why the spaces? Any potential problems with spaces?

Look at some comments

ufo_colors$comments[1:10]

##  [1] "a bright orange color changing to reddish color disk/saucer was observed hovering above power transmission lines."                                  
##  [2] "round &#44 orange &#44 with what i would say was polished metal of some kind around the edges ."                                                    
##  [3] "silent red /orange mass of energy floated by three of us in western north carolina in the 60s"                                                      
##  [4] "oh&#44 what a night &#33  two (2) saucer-shaped&#44 glowing green objects and one (1) brilliantly glowing sphere gliding over the lake."            
##  [5] "a red glowing sphere stopped and watched me."                                                                                                       
##  [6] "three light in the sky that led to a big black silent triangle shaped craft."                                                                       
##  [7] "football field sized chevron with bright white lights on front&#44 moving slowly with absolutely no sound"                                          
##  [8] "a silent black triangular object with no lights moved over us as we walked our dog across a school playground."                                     
##  [9] "1 object with green and red lights"                                                                                                                 
## [10] "man  on hwy 43 sw of milwaukee sees large&#44 bright blue light streak by his car&#44 descend&#44 turn&#44 cross road ahead&#44 strobe. bizarre&#33"

Extract the colors

colors_ans <- map(colors, function(color){str_extract(ufo_colors$comments, color)})

colors_df <- as.data.frame(colors_ans) 
colors_df <- colors_df %>%
  unite("colors_included", 1:ncol(colors_df), sep = ";") %>%
  mutate(colors_included = str_replace_all(colors_included, "NA;", "")) %>%
  mutate(colors_included = str_replace_all(colors_included, ";NA", ""))

head(colors_df)

##   colors_included
## 1         orange 
## 2         orange 
## 3            red 
## 4          green 
## 5            red 
## 6          black

Add colors to original data

ufo_colors <- ufo_colors %>% mutate(colors = colors_df$colors_included)

Working with text data

“You never get better at regex; you just get better at googling.”

http://stringr.tidyverse.org

https://www.tidytextmining.com/

Put it together to answer a question

What colors are most common in UFOs, and is there a relationship between color and shape?

library(tidytext)
colors_df <- ufo_colors %>% 
  unnest_tokens(output = colors, input = colors, 
                token = stringr::str_split, pattern = ";") %>%
  mutate(colors = str_replace_all(colors, " ", ""))

Most common UFO colors

colors_df %>% 
  group_by(colors) %>% 
  count(sort = T) %>% 
  ggplot(aes(x = colors, y = n)) + 
  geom_col() + 
  coord_flip()

Looks like white UFOs are the most common

but red and orange show up a lot too!

Is there a relationship between color and shape?

First, we should just get the 5 most common shapes.

Challenge: How would you do that?

Most common shapes

shapes <- colors_df %>% 
  filter(!is.na(shape)) %>%
  group_by(shape) %>%
  count(sort = TRUE) %>%
  ungroup() %>%
  slice(1:5)

kable(shapes)

What’s wrong with this?

Keep only data with shapes in the top 5.

Challenge: How would you do that?

Keep only data with shapes in the top 5.

shape_colors <- colors_df %>%
  filter(shape %in% shapes$shape)

Relationship between shape and color:

ggplot(shape_colors, aes(x = colors, fill = shape)) + 
  geom_bar(position = "fill")

If extra time…

• What’s another question we could ask of this data?

• What are the programming steps we would need to answer it?

Review

What do each of these functions do?

• mutate()

• filter()

• select()

• group_by() %>% summarise()

• str_select() or str_extract()

• join()

Review: iteration

What are some approaches to iteration?

• for loops

• apply() functions

• map() functions

• in simple cases, may be replacable with group_by() %>% summarise()

Recommended resources

• R for Data Science: http://r4ds.had.co.nz/

• tidyverse web site: https://www.tidyverse.org/

Questions and comments?

Thanks!